﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ThangNM_Client.SharedLibraries
{
    public class QuerySQLiteLIB
    {
        public class Option
        {
            public const String InsertItem = "INSERT INTO tblOption (tid, lid, rid, sLeftName, sRightName, sData, bStatus, tCreate, tUpdate, iSync) " +
                                        "SELECT {0}, {1}, {2}, '{3}', '{4}', '{5}', {6}, '{7}', '{8}', {9} " +
                                        "WHERE NOT EXISTS(SELECT 1 FROM tblOption WHERE (tid = {10}) AND (lid = {11}) AND (rid = {12}));";

            public const String SelectBy_tid_lid_rid = "SELECT rid AS iFID, MAX(sData) AS sFToken " +
                                        "FROM tblOption " +
                                        "WHERE (tid = {0}) AND (bStatus = 1) AND (sData IS NOT NULL OR sData NOT LIKE '') " +
                                        "GROUP BY rid;";

            public const String SelectBy_user_id = "SELECT b.* " +
                                        "FROM tblOption a INNER JOIN tblPage b ON (a.rid = b.id) " +
                                        "WHERE (tid = 1) AND (lid = {0})";

            public const String SelectBy_page_id = "SELECT * " +
                                        "FROM tblPost " +
                                        "WHERE (page_id = {0})";

            public const String SelectBy_post_id = "SELECT * " +
                                        "FROM tblComment " +
                                        "WHERE (post_id = {0})";

            public const String SelectBy_comment_id = "SELECT * " +
                                        "FROM tblComment " +
                                        "WHERE (comment_id = {0})";
        }

        public class Account
        {
            public const String InsertItem = "INSERT INTO tblAccount (PK_iAID, sUsername, sPassword, sEmail, sAvatar, iFID, sFLink, sFName, sFToken, tRegDate, bStatus, iSync) " +
                                        "SELECT {0}, '{1}', '{2}', '{3}', '{4}', {5}, '{6}', '{7}', '{8}', '{9}', {10}, {11} " +
                                        "WHERE NOT EXISTS(SELECT 1 FROM tblAccount WHERE (PK_iAID = {12}));";

            public const String SelectItem = "SELECT * " +
                                        "FROM tblAccount " +
                                        "WHERE (PK_iAID = {0});";
        }

        public class User
        {
            public const String InsertItem = "INSERT INTO tblUser (id, first_name, last_name, link, middle_name, name, timezone, updated_time, verified, sync) " +
                                        "SELECT {0}, '{1}', '{2}', '{3}', '{4}', '{5}', {6}, '{7}', {8}, {9} " +
                                        "WHERE NOT EXISTS(SELECT 1 FROM tblUser WHERE (id = {10}));";
        }

        public class Page
        {
            public const String InsertItem = "INSERT INTO tblPage (id, about, birthday, can_post, category, checkins, country_page_likes, cover, description, " +
                                        "has_added_app, is_community_page, is_published, new_like_count, likes, link, name, offer_eligible, parking, promotion_eligible, " +
                                        "talking_about_count, unread_message_count, unread_notif_count, unseen_message_count, username, website, were_here_count, sync) " +
                                        "SELECT {0}, '{1}', '{2}', {3}, '{4}', {5}, {6}, '{7}', '{8}', " +
                                        "{9}, {10}, {11}, {12}, {13}, '{14}', '{15}', {16}, '{17}', {18}, " +
                                        "{19}, {20}, {21}, {22}, '{23}', '{24}', {25}, {26} " +
                                        "WHERE NOT EXISTS(SELECT 1 FROM tblPage WHERE (id = {27}));";
        }

        public class Post
        {
            public const String InsertItem = "INSERT INTO tblPost (id, page_id, user_id, user_name, user_category, message, updated_time, sync) " +
                                        "SELECT {0}, {1}, {2}, '{3}', '{4}', '{5}', '{6}', {7} " +
                                        "WHERE NOT EXISTS(SELECT 1 FROM tblPost WHERE (id = {8}));";
        }

        public class Comment
        {
            public const String InsertItem = "INSERT INTO tblComment (id, page_id, post_id, comment_id, user_id, user_name, created_time, message, can_remove, like_count, message_tags, user_likes, comment_count, sync) " +
                                        "SELECT {0}, {1}, {2}, {3}, {4}, '{5}', '{6}', '{7}', {8}, {9}, '{10}', {11}, {12}, {13} " +
                                        "WHERE NOT EXISTS(SELECT 1 FROM tblComment WHERE (id = {14}));";

            public const String SelectBy_Key_Email = "SELECT * FROM tblComment WHERE message LIKE '%@%' ORDER BY id LIMIT {0} OFFSET {1};";
        }

        public class Like
        {
            public const String InsertItem = "INSERT INTO tblLike (post_id, user_id, page_id, user_name, sync) " +
                                        "SELECT {0}, {1}, {2}, '{3}', {4} " +
                                        "WHERE NOT EXISTS(SELECT 1 FROM tblLike WHERE (post_id = {5}) AND (user_id = {6}));";
        }

        public class Email
        {
            public const String InsertItem = "INSERT INTO tblEmail (iFID, sFName, sFEmail, bValid) " +
                                        "VALUES ({0}, '{1}', '{2}', {3});";
        }

        public class PHD
        {
            public const String InsertItem = "INSERT INTO tblPHD (PK_iVID, sTitle, sDescription, sKeyword, sInfoEx, sUrl, sImagePoster, sImageSmall, sImageMedium, sImageLarge, sVideo240p, sVideo480p, sVideo720p, sVideo1080p, iVideoServerId) " +
                                        "SELECT {0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', {14} " +
                                        "WHERE NOT EXISTS(SELECT 1 FROM tblPHD WHERE (PK_iVID = {15}));";


            public const String Update_Info_Video = "UPDATE tblPHD "+
                                        "SET sVideo240p = '{0}', sVideo480p = '{1}', sVideo720p = '{2}', sVideo1080p = '{3}' " +
                                        "WHERE (PK_iVID = {4});";

            public const String SelectItem = "SELECT * FROM tblPHD WHERE (PK_iVID = {0});";

            /// <summary>
            /// {0}: Order
            /// {1}: PageSize
            /// {2}: PageNumber
            /// </summary>
            public const String SelectBy_Page_Default = "SELECT PK_iVID, sTitle, sImageSmall FROM tblPHD ORDER BY {0} DESC LIMIT {1} OFFSET {2}; SELECT COUNT(1) AS ROWCOUNT FROM tblPHD;";

            /// <summary>
            /// {0}: Query
            /// {1}: Query
            /// {2}: Order
            /// {3}: PageSize
            /// {4}: PageNumber
            /// </summary>
            public const String SelectBy_Page_Search = "SELECT * FROM tblPHD WHERE ((sTitle LIKE '%{0}%') AND (sDescription LIKE '%{1}%')) ORDER BY {2} DESC LIMIT {3} OFFSET {4};";
        }
    }
}
